package fixtures;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Messages.MessageLendBook;
import Messages.MessageOrderBook;
import server.SQLConnection;
import fit.ActionFixture;

public class OrderBook extends ActionFixture {
	public SQLConnection conn;
	private String id,copy,x,BID,id2; 
	public Statement stmt;
	PreparedStatement ps,ps3;
	
	
	public MessageLendBook lend=new MessageLendBook();
	public MessageOrderBook ob= new MessageOrderBook();
	
	public Boolean lendAllcopys() throws SQLException, IOException{
		
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs1= stmt.executeQuery("select * from db1.subscriber ");
		rs1.next();
		id=rs1.getString(1);
		ps=conn.getConnection().prepareStatement("SELECT * FROM db1.subscriber WHERE ID= ? And Status= ?;");
		ps.setString(1,id);
		ps.setString(2,"0");
		ResultSet rs = ps.executeQuery(); 
		
		java.sql.ResultSet rs2= stmt.executeQuery("select db1.bookscopies.BID from db1.bookscopies ");
		rs2.next();
		BID=rs2.getString(1);
		java.sql.ResultSet rs3= stmt.executeQuery("select db1.bookscopies.CopyId from db1.bookscopies where db1.bookscopies.BID='"+BID+"' ");
		
		while (rs3.next())
		{
			lendcopys(rs3.getString(1));
		}
		
		return checkAllLend(BID);
	}
	
	
	
	
	
	
	public String lendcopys(String copyid) throws SQLException, IOException {
		
		
		copy=copyid;
	    String[] str2= {"123", id,copy};
	    x = lend.checklend(str2);
	    return x;
	    
	    
		}
	
	
	
	public Boolean checkAllLend (String BID) throws SQLException
	{
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs4= stmt.executeQuery("select count(db1.bookscopies.copyid) as count from db1.bookscopies  where db1.bookscopies.BID='"+BID+"';");
		rs4.next();
		int c=rs4.getInt(1);
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs5= stmt.executeQuery("select count(db1.bookscopies.copyid) as count from db1.bookscopies,db1.books,db1.lending where db1.bookscopies.BID=db1.books.ID and db1.bookscopies.copyid=db1.lending.copyid and db1.lending.lend='yes' and db1.books.ID='"+BID+"';");
		rs5.next();
		int d=rs5.getInt(1);
		if (c==d) return true;
		else return false;
		
	}
	public boolean SelectNewUser() throws SQLException
	{
		stmt = conn.getConnection().createStatement();
		java.sql.ResultSet rs1= stmt.executeQuery("select * from db1.subscriber ");
		rs1.next();
		rs1.next();
		id2=rs1.getString(1);
		ps=conn.getConnection().prepareStatement("SELECT * FROM db1.subscriber WHERE ID= ? And Status= ?;");
		ps.setString(1,id2);
		ps.setString(2,"0");
		ResultSet rs = ps.executeQuery();
		if (id!=id2) return true;
		else return false;
	}
	
	public boolean MakeOrder () throws SQLException, IOException
	{
		java.sql.ResultSet rs6= stmt.executeQuery("select db1.bookscopies.CopyId from db1.bookscopies where db1.bookscopies.BID='"+BID+"' ");
		rs6.next();
		copy=rs6.getString(1);
		ArrayList<String> arr=ob.MakeOrder(id2,copy);
		if (arr.contains("0")) return true;
		else return false;
	}
	
	public boolean MakeAnotherOrder () throws SQLException, IOException
	{
	
		ArrayList<String> arr=ob.MakeOrder(id2,copy);
		if (arr.contains("1")) return true;
		else return false;
	}
	
	
	public boolean startcon(){
		
		
		conn = new SQLConnection();
		return true;
		
	}
	
}
